USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[KonseiSyumoku] スクリプト日付: 2014/05/09 15:09:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KonseiSyumoku]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[KonseiSyumoku]
GO

CREATE PROCEDURE [dbo].[KonseiSyumoku]
	@TAIKAINO int,
	@KYOGINO int
AS
	SELECT 
	CC.SYUMOKUNO,
	CC.SYUMOKUMEI,
	G.KYOGIFLG,
	CC.SYUMOKUKUBUN,
	CC.KIROKUKUBUN 
FROM 
	TBL_KYOGI AS AA 
LEFT JOIN 
	TBL_KONSEI_MASTER AS BB 
	ON AA.SYUMOKUNO = BB.SYUMOKUNO 
LEFT JOIN 
	TBL_SYUMOKU_MASTER AS CC 
	ON BB.KONSEINO = CC.SYUMOKUNO 
LEFT JOIN 
	(SELECT B.TAIKAINO,
		B.KYOGINO,
		B.SUBNO,
		B.ROUNDNO,
		B.KYOGIFLG 
	FROM 
		TBL_ROUND AS B 
	RIGHT JOIN 
		(SELECT 
			A.TAIKAINO,
			A.KYOGINO,
			A.SUBNO,
			MAX(A.ROUNDNO) AS FROUND 
		FROM 
			TBL_ROUND AS A 
		WHERE 
			A.TAIKAINO = @TAIKAINO 
			AND A.KYOGINO = @KYOGINO 
		GROUP BY 
			A.TAIKAINO,
			A.KYOGINO,
		A.SUBNO ) AS C 
		ON B.TAIKAINO = C.TAIKAINO 
		AND B.KYOGINO = C.KYOGINO 
		AND B.SUBNO = C.SUBNO 
	AND B.ROUNDNO = C.FROUND) AS G 
		ON G.TAIKAINO = AA.TAIKAINO 
		AND G.KYOGINO = AA.KYOGINO 
		AND BB.KONSEINO = G.SUBNO 
WHERE 
	AA.TAIKAINO = @TAIKAINO 
	AND AA.KYOGINO = @KYOGINO 
	ORDER BY BB.ID
